package com.erp.dao.impl;

import com.erp.bean.Order;
import com.erp.dao.OrderDao;
import com.erp.util.db.DataBaseDao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

/**
 * @author lpy
 * 订单数据访问实现类
 */
public class OrderDaoImpl extends DataBaseDao implements OrderDao {

    @Override
    public int add(Order order) throws SQLException {
        //获取数据库连接对象
        buildConnection();
        String sql = "insert into orders(oid,sid,eid,buytime,gid,count) value(?,?,?,?,?,?)";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //设置参数
        pstmt.setString(1,order.getOid());
        pstmt.setInt(2,order.getSid());
        pstmt.setInt(3,order.getEid());
        pstmt.setTimestamp(4,order.getBuytime());
        pstmt.setInt(5,order.getGid());
        pstmt.setInt(6,order.getCount());
        //执行新增
        return pstmt.executeUpdate();
    }

    @Override
    public int update(Order order) throws SQLException {
        //获取数据库连接对象
        buildConnection();
        String sql = "update orders set sid=?,eid=?,gid=?,count=? where oid=?";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //设置参数
        pstmt.setInt(1,order.getSid());
        pstmt.setInt(2,order.getEid());
        pstmt.setInt(3,order.getGid());
        pstmt.setInt(4,order.getCount());
        pstmt.setString(5,order.getOid());
        //执行修改
        return pstmt.executeUpdate();
    }

    @Override
    public int updateBatch(Set<String> oids,int sid) throws SQLException {
        //获取数据库连接对象
        buildConnection();
        String sql = "update orders set sid=? where oid in(";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //设置sql中的占位符?
        for (int i = 0; i < oids.size(); i++) {
            sql+="?,";
        }
        //去除最后一个",",并在末尾添加")"
        String str = sql.substring(0,sql.lastIndexOf(","))+")";
        pstmt = conn.prepareStatement(str);
        //设置sql语句的参数
        pstmt.setInt(1,sid);
        Iterator<String> it = oids.iterator();
        int index = 2;
        while (it.hasNext()){
            pstmt.setString(index++,it.next());
        }
        //执行批量修改
        return pstmt.executeUpdate();
    }

    @Override
    public Order findById(String oid) throws SQLException {
        //获取数据库连接对象
        buildConnection();
        Order order = null;
        String sql = "select oid,sid,eid,buytime,gid,count from orders where oid=?";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //设置参数
        pstmt.setString(1,oid);
        rs = pstmt.executeQuery();
        if (rs.next()){
            order = new Order();
            order.setOid(rs.getString(1));
            order.setSid(rs.getInt(2));
            order.setEid(rs.getInt(3));
            order.setBuytime(rs.getTimestamp(4));
            order.setGid(rs.getInt(5));
            order.setCount(rs.getInt(6));
        }
        return order;
    }

    @Override
    public List<Order> findAll() throws SQLException {
        //获取数据库连接对象
        buildConnection();
        List<Order>  orders = new ArrayList<>();
        String sql = "select oid,sid,eid,buytime,gid,count from orders";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //执行查询 并处理结果
        rs = pstmt.executeQuery();
        while(rs.next()){
            Order order = new Order();
            order.setOid(rs.getString(1));
            order.setSid(rs.getInt(2));
            order.setEid(rs.getInt(3));
            order.setBuytime(rs.getTimestamp(4));
            order.setGid(rs.getInt(5));
            order.setCount(rs.getInt(6));
            orders.add(order);
        }
        return orders;
    }

    @Override
    public List<Order> findAllSpilt(Integer currentPage, Integer pageSize) throws SQLException {
        //获取数据库连接对象
        buildConnection();
        List<Order>  orders = new ArrayList<>();
        String sql = "select oid,sid,eid,buytime,gid,count from orders limit ?,?";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //设置参数
        pstmt.setInt(1,(currentPage-1)*pageSize);
        pstmt.setInt(2,pageSize);
        //执行查询 并处理结果
        rs = pstmt.executeQuery();
        while (rs.next()){
            Order order = new Order();
            order.setOid(rs.getString(1));
            order.setSid(rs.getInt(2));
            order.setEid(rs.getInt(3));
            order.setBuytime(rs.getTimestamp(4));
            order.setGid(rs.getInt(5));
            order.setCount(rs.getInt(6));
            orders.add(order);
        }
        return orders;
    }

    @Override
    public List<Order> findAllSpilt(Integer currentPage, Integer pageSize, String column, String keyWords) throws SQLException {
        //获取数据库连接对象
        buildConnection();
        List<Order>  orders = new ArrayList<>();
        String sql = "";
        if (column.equals("gname")){
            sql = "SELECT oid,sid,eid,buytime,gid,count FROM orders WHERE gid IN(SELECT gid FROM commodity WHERE gname LIKE ?) limit ?,?";
        }else if(column.equals("eid")){
            sql = "SELECT oid,sid,eid,buytime,gid,count FROM orders WHERE eid IN(SELECT eid FROM emp WHERE ename LIKE ?) limit ?,?";
        }else if(column.equals("state")){
            sql = "SELECT oid,sid,eid,buytime,gid,count FROM orders WHERE sid IN(SELECT sid FROM orderstate WHERE state LIKE ?) limit ?,?";
        }

        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //设置参数
        pstmt.setString(1,"%"+keyWords+"%");
        pstmt.setInt(2,(currentPage-1)*pageSize);
        pstmt.setInt(3,pageSize);
        //执行查询 并处理结果
        rs = pstmt.executeQuery();
        while (rs.next()){
            Order order = new Order();
            order.setOid(rs.getString(1));
            order.setSid(rs.getInt(2));
            order.setEid(rs.getInt(3));
            order.setBuytime(rs.getTimestamp(4));
            order.setGid(rs.getInt(5));
            order.setCount(rs.getInt(6));
            orders.add(order);
        }
        return orders;
    }

    @Override
    public Long getAllCount() throws SQLException {
        //获取数据库连接对象
        buildConnection();
        Long num = 0L;
        String sql = "select count(*) from orders";
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //执行查询
        rs = pstmt.executeQuery();
        if (rs.next()){
            num = rs.getLong(1);
        }
        return num;
    }

    @Override
    public Long getAllCount(String colunm, String keyWords) throws SQLException {
        //获取数据库连接对象
        buildConnection();
        Long num = 0L;
        String sql = "";
        if (colunm.equals("gname")){
            sql = "SELECT count(*) FROM orders WHERE gid IN(SELECT gid FROM commodity WHERE gname LIKE ?)";
        }else if(colunm.equals("eid")){
            sql = "SELECT count(*) FROM orders WHERE eid IN(SELECT eid FROM emp WHERE ename LIKE ?)";
        }else if(colunm.equals("state")){
            sql = "SELECT count(*) FROM orders WHERE sid IN(SELECT sid FROM orderstate WHERE state LIKE ?)";
        }
        //创建预编译对象
        pstmt = conn.prepareStatement(sql);
        //设置参数
        pstmt.setString(1,"%"+keyWords+"%");
        //执行查询
        rs = pstmt.executeQuery();
        if (rs.next()){
            num = rs.getLong(1);
        }
        return num;
    }
}
